﻿/*
 * AccessAccess.cs
 * Written by: Shawn Gilleland
 * 
 * Unfortunate mix of database type and Bridge Pattern
 */

using Supply_Fetch;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace StorageLayer
{
    class AccessAccess:DBAccessImpl
    {
        /*
         * Note to self: this class might be assigned too many
         * responsibilites, consider letting the Item and Drawer
         * classes handle some functions themselves.
         */

        /// <summary>
        /// Needs modification
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public List<Drawer> FindItem(Item item)
        {
            List<Drawer> drawers = new List<Drawer>();

            //if the item's name is empty, don't even bother searching database
            if (!(item.GetName().Length > 0))
                return drawers;

            OleDbConnection conn = DBConnect();
            string SqlString = "SELECT Row, Column FROM Drawer INNER JOIN Item ON Drawer.ID = Item.Drawer_ID WHERE Item.Item_Name = ?";

            using (conn)
            {
                using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("Item.Item_Name", item.GetName());

                    conn.Open();
                    using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            drawers.Add(new Drawer(Convert.ToInt32(reader["Row"].ToString()), Convert.ToInt32(reader["Column"].ToString())));
                        }
                    }
                    
                    conn.Close();
                }
            }
            return drawers;
        }

        /// <summary>
        /// Needs modification
        /// </summary>
        /// <param name="item"></param>
        /// <param name="drawer"></param>
        /// <returns></returns>
        public bool AddItem(Item item, Drawer drawer)
        {
            bool drawerExists = false;
            bool itemInDrawer = true;
            bool itemAddedToDrawer = false;

            int drawerID = 0;

            //check if item name is empty, or if drawer row/column out of bounds, item adding fails
            if (!(item.GetName().Length > 0) || drawer.GetRow() < Constants.MIN_DRAWER_ROW || drawer.GetRow() > Constants.MAX_DRAWER_ROW || 
                drawer.GetColumn() < Constants.MIN_DRAWER_COLUMN || drawer.GetColumn() > Constants.MAX_DRAWER_COLUMN)
                return false;

            OleDbConnection conn = DBConnect();

            string checkDrawerExistsQuery = "SELECT * FROM Drawer WHERE Column = ? AND Row = ?";
            string checkItemExistsQuery = "SELECT * FROM Item WHERE Item_Name = ? AND Drawer_ID = ?";
            string addItemQuery = "INSERT INTO Item (Item_Name, Drawer_ID) VALUES (?, ?)";

            using (conn)
            {
                conn.Open();

                using (OleDbCommand drawerExistsCmd = new OleDbCommand(checkDrawerExistsQuery, conn))
                {
                    drawerExistsCmd.CommandType = CommandType.Text;
                    drawerExistsCmd.Parameters.AddWithValue("Column", drawer.GetColumn());
                    drawerExistsCmd.Parameters.AddWithValue("Row", drawer.GetRow());

                    OleDbDataReader drawerExistsReader = drawerExistsCmd.ExecuteReader();
                    if (drawerExistsReader.Read())
                    {
                        // store the ID of the drawer
                        Console.WriteLine("\n\n" + drawerExistsReader["ID"].ToString());
                        drawerID = Convert.ToInt32(drawerExistsReader["ID"].ToString());
                        drawerExists = true;
                    }
                    else
                        drawerExists = false;
                }

                if (drawerExists)
                {
                    using (OleDbCommand itemExistsCmd = new OleDbCommand(checkItemExistsQuery, conn))
                    {
                        itemExistsCmd.CommandType = CommandType.Text;
                        itemExistsCmd.Parameters.AddWithValue("Item_Name", item.GetName().ToLower());
                        itemExistsCmd.Parameters.AddWithValue("Drawer_ID", drawerID);

                        OleDbDataReader itemExistsReader = itemExistsCmd.ExecuteReader();
                        if (itemExistsReader.HasRows)
                        {
                            itemInDrawer = true;
                        }
                        else
                        {
                            itemInDrawer = false;
                        }
                    }
                }

                if (!itemInDrawer)
                {
                    using (OleDbCommand addItemCmd = new OleDbCommand(addItemQuery, conn))
                    {
                        addItemCmd.CommandType = CommandType.Text;
                        addItemCmd.Parameters.AddWithValue("Item_Name", item.GetName().ToLower());
                        addItemCmd.Parameters.AddWithValue("Drawer_ID", drawerID);

                        addItemCmd.ExecuteNonQuery();

                        itemAddedToDrawer = true;
                    }
                }

                conn.Close();
            }

            return itemAddedToDrawer;
        }

        /// <summary>
        /// Needs modification
        /// </summary>
        /// <param name="item"></param>
        /// <param name="drawer"></param>
        /// <returns></returns>
        public bool RemoveItem(Item item, Drawer drawer)
        {
            //http://forums.devshed.com/net-development-87/oledbdatareader-no-data-exists-for-row-column-when-it-does-560997.html
            bool drawerExists = false;
            bool itemInDrawer = false;
            bool itemRemovedFromDrawer = false;

            int drawerID = 0;

            //check if item name is empty, or if drawer row/column out of bounds, item removing fails
            if (!(item.GetName().Length > 0) || drawer.GetRow() < Constants.MIN_DRAWER_ROW || drawer.GetRow() > Constants.MAX_DRAWER_ROW ||
                drawer.GetColumn() < Constants.MIN_DRAWER_COLUMN || drawer.GetColumn() > Constants.MAX_DRAWER_COLUMN)
                return false;

            OleDbConnection conn = DBConnect();

            string checkDrawerExistsQuery = "SELECT * FROM Drawer WHERE Column = ? AND Row = ?";
            string checkItemExistsQuery = "SELECT * FROM Item WHERE Item_Name = ? AND Drawer_ID = ?";
            string removeItemQuery = "DELETE FROM Item WHERE Item_Name = ? AND Drawer_ID = ?";

            using (conn)
            {
                conn.Open();

                using (OleDbCommand drawerExistsCmd = new OleDbCommand(checkDrawerExistsQuery, conn))
                {
                    drawerExistsCmd.CommandType = CommandType.Text;
                    drawerExistsCmd.Parameters.AddWithValue("Column", drawer.GetColumn());
                    drawerExistsCmd.Parameters.AddWithValue("Row", drawer.GetRow());

                    OleDbDataReader drawerExistsReader = drawerExistsCmd.ExecuteReader();
                    if (drawerExistsReader.Read())
                    {
                        // store the ID of the drawer
                        drawerID = Convert.ToInt32(drawerExistsReader["ID"].ToString());
                        drawerExists = true;
                    }
                    else
                        drawerExists = false;
                }

                if (drawerExists)
                {
                    using (OleDbCommand itemExistsCmd = new OleDbCommand(checkItemExistsQuery, conn))
                    {
                        itemExistsCmd.CommandType = CommandType.Text;
                        itemExistsCmd.Parameters.AddWithValue("Item_Name", item.GetName().ToLower());
                        itemExistsCmd.Parameters.AddWithValue("Drawer_ID", drawerID);

                        OleDbDataReader itemExistsReader = itemExistsCmd.ExecuteReader();
                        if (itemExistsReader.HasRows)
                        {
                            itemInDrawer = true;
                        }
                        else
                        {
                            itemInDrawer = false;
                        }
                    }
                }

                if (itemInDrawer)
                {
                    using (OleDbCommand removeItemCmd = new OleDbCommand(removeItemQuery, conn))
                    {
                        removeItemCmd.CommandType = CommandType.Text;
                        removeItemCmd.Parameters.AddWithValue("Item_Name", item.GetName().ToLower());
                        removeItemCmd.Parameters.AddWithValue("Drawer_ID", drawerID);

                        removeItemCmd.ExecuteNonQuery();

                        itemRemovedFromDrawer = true;
                    }
                }

                conn.Close();
            }

            return itemRemovedFromDrawer;
        }

        /// <summary>
        /// Needs modification
        /// </summary>
        /// <returns></returns>
        public List<Drawer> LoadItems()
        {
            List<Drawer> drawers = new List<Drawer>();

            OleDbConnection conn = DBConnect();

            String getDrawersQuery = "SELECT * From Drawer";
            String getItemsQuery = "SELECT * From Item WHERE Drawer_ID = ?";

            using (conn)
            {
                conn.Open();
                using (OleDbCommand getDrawersCmd = new OleDbCommand(getDrawersQuery, conn))
                {
                    getDrawersCmd.CommandType = CommandType.Text;

                    using (OleDbDataReader getDrawersReader = getDrawersCmd.ExecuteReader())
                    {
                        while (getDrawersReader.Read())
                        {
                            int drawerRow = Convert.ToInt32(getDrawersReader["Row"].ToString());
                            int drawerCol = Convert.ToInt32(getDrawersReader["Column"].ToString());
                            List<Item> itemsInDrawer = new List<Item>();

                            using (OleDbCommand getItemsCmd = new OleDbCommand(getItemsQuery, conn))
                            {
                                getItemsCmd.CommandType = CommandType.Text;
                                getItemsCmd.Parameters.AddWithValue("Drawer_ID", Convert.ToInt32(getDrawersReader["ID"].ToString()));

                                using (OleDbDataReader getItemsReader = getItemsCmd.ExecuteReader())
                                {
                                    while (getItemsReader.Read())
                                    {
                                        itemsInDrawer.Add(new Item(getItemsReader["Item_Name"].ToString().ToLower()));
                                    }
                                }
                            }

                            drawers.Add(new Drawer(drawerRow, drawerCol, itemsInDrawer));
                        }
                    }
                }
                conn.Close();
            }
            return drawers;
        }

        /// <summary>
        /// Query to insert item to the requests table for metrics reporting
        /// </summary>
        /// <param name="item"></param>
        public void RecordItemRequest(Item item)
        {
            OleDbConnection conn = DBConnect();

            string sql = "INSERT INTO Request (Item_Name) VALUES(?)";
            using (conn)
            {
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("Item_Name", item.GetName().ToLower());
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }

        private OleDbConnection DBConnect()
        {
            OleDbConnection conn;
            string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database.MDB";

            conn = new OleDbConnection(strAccessConn);

            return conn;
        }
    }
}
